using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for TaiKhoanCuaToi
/// </summary>
public class TaiKhoanCuaToi
{
	public TaiKhoanCuaToi()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    private DBconnect DB = new DBconnect();

    /// <summary>
    /// So luong san tham gia thi dau.....
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    
    public DataTable SoLuongSanById(int id)
    {
        DB.Open();
        string sql = "select count( auction_sale_detail.users_id) as SoLuongThamGia from auction_sale_detail where auction_sale_detail.users_id =" +id + " Group by auction_sale_detail.users_id ";
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        return kq;
    }

    public int SoLuongSanDangThamGia(int id)
    {
        DB.Open();
        string sql = "select ID from auction_sale_detail where users_id =" + id;
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        int temp = kq.Rows.Count;
        return temp;
    }

    /// <summary>
    /// So lan thang cuoc va tong tien
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>

    public DataTable SoLuongSanMaxById(int id)
    {
        DB.Open();
        string sql = " Select count(auction_sale.winner_id) as SoLan,sum(auction_sale.price_to_buy) as Tongtien From  auction_sale Where  auction_sale.status= 0 and auction_sale.winner_id" + id + " Group By auction_sale.winner_id,auction_sale.price_to_buy ";
        DataTable kq=new DataTable();
        kq=DB.GetDataTable(sql);
        return kq;
    }

    /// <summary>
    /// So luong san dang ban
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    
    public DataTable SoLuongSanDangBanById(int id)
    {
        DB.Open();
        string sql = "select count(auction_sale.seller_id) as SoSangDangBan from auction_sale where auction_sale.seller_id =  " + id + " and auction_sale.status = 1 group by auction_sale.seller_id ";
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        return kq;
    }

    public int SoLuongSanDangBan(int id)
    {
        DB.Open();
        string sql = "select ID from auction_sale where seller_id =  " + id + " and status = 1";
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        return kq.Rows.Count;
    }

    public DataTable DanhSachSanDangBan(int userID, int value)
    {
        DB.Open();        
        string sql;
        if (value == 1)
        {
            sql = "select a.ID, p.image_url as image, p.name as name, a.current_rate as rate, u.username as username,(Select count(*) from auction_sale_detail asd where asd.auction_sale_id = a.ID) as soluong from auction_sale a, products p, users u where a.seller_id = " + userID + " and a.status = 1 and a.products_id = p.ID and u.ID = a.winner_id and a.status = 1";
        }
        else
        {
            sql = "select a.ID, p.image_url as image, p.name as name, a.current_rate as rate, u.username as username,(Select count(*) from auction_sale_detail asd where asd.auction_sale_id = a.ID) as soluong from auction_sale a, products p, users u where a.seller_id = " + userID + " and a.status = 1 and a.products_id = p.ID and u.ID = a.winner_id and a.status = 1 and datediff(day, p.date_added, getdate()) <= 30";
        }
        
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);        
        return kq;
    }
    public DataTable DanhSachSanDaBan(int userId, int value)
    {
        DB.Open();
        DataTable kq = new DataTable();
        string sql;
        if (value == 1)
        {
            sql = "select a.ID, u.username as username, a.current_rate as rate, a.time_expire as date From auction_sale a, users u Where u.ID = a.winner_id and a.seller_id = " + userId + " and a.status = 0 and a.current_rate >= a.price_expect";
        }
        else
        {
            sql = "select a.ID, u.username as username, a.current_rate as rate, a.time_expire as date From auction_sale a, users u Where u.ID = a.winner_id and a.seller_id = " + userId + " and a.status = 0 and a.current_rate >= a.price_expect and datediff(day, a.time_begin, getdate()) <= 30";
        }      
        kq = DB.GetDataTable(sql);
        return kq;
    }
    public DataTable DanhSachSanKhongBan(int userId, int value)
    {
        DB.Open();
        DataTable kq = new DataTable();
        string sql;
        if (value == 1)
        {
            sql = "select a.ID, p.image_url as image, p.name as name, a.current_rate as rate, a.time_expire as date,(Select count(*) from auction_sale_detail asd where asd.auction_sale_id = a.ID) as soluong from auction_sale a, products p where a.seller_id = " + userId + " and a.products_id = p.ID and a.status = 0 and a.current_rate < a.price_expect";
        }
        else
        {
            sql = "select a.ID, p.image_url as image, p.name as name, a.current_rate as rate, a.time_expire as date,(Select count(*) from auction_sale_detail asd where asd.auction_sale_id = a.ID) as soluong from auction_sale a, products p where a.seller_id = " + userId + " and a.products_id = p.ID and a.status = 0 and a.current_rate < a.price_expect and datediff(day, p.date_added, getdate()) <= 30";
        }
        
        kq = DB.GetDataTable(sql);
        return kq;
    }
    public DataTable SoLuongSanDaBanById(int id)
    {
        DB.Open();
        string sql = "select count(auction_sale.seller_id) as SoSangDangBan from auction_sale where auction_sale.seller_id =  " + id + " and auction_sale.status = 0 group by auction_sale.seller_id ";
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        return kq;
    }

    public int SoLuongSanDaBan(int id)
    {
        DB.Open();
        string sql = "select ID from auction_sale where seller_id =  " + id + " and status = 0";
        DataTable kq = new DataTable();
        kq = DB.GetDataTable(sql);
        return kq.Rows.Count;
    }
}
